
/****** Object:  StoredProcedure [dbo].[GetCustomers]    Script Date: 2/12/2015 5:54:05 PM ******/
DROP PROCEDURE [dbo].[GetCustomers]
GO

/****** Object:  StoredProcedure [dbo].[GetCustomers]    Script Date: 2/12/2015 5:54:05 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[GetCustomers]	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--For send mail
    ;WITH TempResult  AS
    (
	SELECT ROW_NUMBER() OVER(PARTITION BY CustomerId ORDER BY NextDueDate) AS "RowNumber", *
	FROM Certificate where DATEDIFF(day,GETDATE(),NextDueDate) > 0
	and DATEDIFF(day,GETDATE(),NextDueDate) < 30
	)
	select T.*, U.Name, U.SalesmanId, A.FirstName, A.LastName, A.Email from TempResult T inner join Customers U on T.CustomerId = U.Id
	inner join AspNetUsers A on U.SalesmanId = A.Id
	where RowNumber=1

	--For update next due date in customer table
	;WITH TempResult  AS
	(
	SELECT ROW_NUMBER() OVER(PARTITION BY CustomerId ORDER BY NextDueDate) AS "RowNumber", *
	FROM Certificate where DATEDIFF(day,GETDATE(),NextDueDate) > 0
	--and DATEDIFF(day,GETDATE(),NextDueDate) < 30
	)
	select T.*, U.Name, U.SalesmanId, Email from TempResult T inner join Customers U on T.CustomerId = U.Id
	where RowNumber=1 and U.NextDueDate < T.NextDueDate and U.NextDueDate < GETDATE()

END


GO


USE [Caltech]
GO

/****** Object:  StoredProcedure [dbo].[UpdateCustomer]    Script Date: 2/12/2015 5:54:24 PM ******/
DROP PROCEDURE [dbo].[UpdateCustomer]
GO

/****** Object:  StoredProcedure [dbo].[UpdateCustomer]    Script Date: 2/12/2015 5:54:24 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[UpdateCustomer]	
	@CustomerId int,		
	@NextDueDate datetime 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    
   UPDATE Customers
   SET NextDueDate = @NextDueDate
	WHERE Customers.Id = @CustomerId


END
GO


/****** Object:  StoredProcedure [dbo].[UpdateSendMailCustomer]    Script Date: 2/12/2015 5:55:34 PM ******/
DROP PROCEDURE [dbo].[UpdateSendMailCustomer]
GO

/****** Object:  StoredProcedure [dbo].[UpdateSendMailCustomer]    Script Date: 2/12/2015 5:55:34 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[UpdateSendMailCustomer]	
	@CustomerId int,		
	@NextDueDate datetime 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    
   UPDATE Customers
   SET SentMailDate = @NextDueDate
   WHERE Customers.Id = @CustomerId


END
GO


